The invention provides a process for analyzing and predicting the performance of a Database Management System (DBMS) through the creation and use of a simulated test DBMS. The relatively small test database system reduces the cost of performing the analysis and minimizes the impact on the actual operating DBMS.
Database systems often contain databases of immense size and complexity. Because of this size and complexity, obtaining the desired information in response to a query becomes a very complex process. This requires, for most queries, that the system must make many choices as to the manner in which to seek the data. In many cases, if the query is complex, it is often difficult to determine whether the database manager has selected the most optimum process for obtaining the required data.
Analyzing the performance of the system to a query could be done by running the query on the operational database system itself. It could also be done using a complete replica of the system. Modifications might also be made in the original database or replica to test and see if the query might be made to run faster. The first approach of using the operational system has an obvious negative impact on the system as it reduces system availability to other users, whereas, in the case of using a separate test system itself, it might be a very expensive proposition.
Another variation of the replica method that exists today is to avoid copying the data from the user tables that contain the data, but instead, creating empty tables and modifying the database catalogs to reflect information (statistics) that is needed by the compiler to match that of the original database. This could also require a large system and more time with the only advantage of reduced storage space as compared to the full replica.
The present invention overcomes this problem by using a test database on which the performance of the database system can be simulated without impacting the operational database system and without the need to create any of the user tables or modify associated information in the catalogs. The invention creates a copy of the database catalogs used in queries as data tables. The test database system can use these simulated catalogs to compile the query in a special simulation mode that does not need to reference the tables in the operational database system.
The invention provides a simulation tool for testing the performance of an operational DBMS where a separate test database is created having a copy of the system catalogs. The copy is stored as a set of user tables called simulation catalogs in the test database. Queries, compiled on the test database in a simulation mode, use the simulated catalogs instead of the normal system catalogs. With this process it is possible to recreate access plans which are equivalent to the access plans which would be generated by the operating DBMS in response to the same query. The invention enables analyzing the access plans without compiling on the operational DBMS and without the need to copy user data onto the test database system.
The present invention provides a system for analyzing the performance of an operational database management system comprising a test database management system including means for storing a copy of system catalog tables as simulated catalog tables in user tables in the test database, means for accepting test queries in the test system, means for compiling the test queries in the test system and means for generating an access plan in response to the test queries whereby analysis of the performance of the operational database management system can be performed.
The invention also describes a process for evaluating the performance of an operational database system which consists of the steps of creating a test database management system, storing a copy of system catalogue tables from said operational database management system in user tables in said test database, compiling queries on said test database and generating access plans for the queries, the access plans being used to evaluate the performance of the operational database management system.
A primary object of the invention is to analyze or predict the performance of an operational DBMS without impacting the operation of the DBMS.
A further object of the invention is to create a copy of catalog tables in an operational DBMS as user tables in a test DBMS to enable query processing in said test DBMS without accessing the operational DBMS during the query processing.
A further object of the invention is to compile test queries for an operational DBMS on a test database and generate access plans for the test queries without accessing the operational DBMS during the compilation.